﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using BLL;
using Common;
using Model;
using WebPortal.Models;
using Npgsql;

namespace WebPortal.Controllers
{
    public class EtlTargetController : BaseController
    {
        private static EncrypCode encrypCode = new EncrypCode();

        #region 对接系统列表
        public ActionResult EtlTargetList(string dbName)
        {
            PageRole role = new PageRole();
            role.role = SysPublicBLL.GetUserRole(Request.Cookies["MDM_UserName"].Value, dbName);
            return View(role);
        }

        /// <summary>
        /// 对接系统列表
        /// </summary>
        /// <param name="code">系统编码</param>
        /// <param name="name">系统名称</param>
        /// <param name="status">状态</param>
        /// <param name="targetType">数据库类型</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult EtlTargetList(string contact, string name, string status, string targetType, string dbName, int pageSize = 10, int pageIndex = 1)
        {
            int targetTypeVal = string.IsNullOrEmpty(targetType) ? 0 : int.Parse(targetType);
            int statusVal = string.IsNullOrEmpty(status) ? 0 : int.Parse(status);
            pageSize = int.Parse(Request.Params["rows"]);
            pageIndex = int.Parse(Request.Params["page"]);
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            if (string.IsNullOrEmpty(sort))
                sort = "CreateTime";
            string baseDB = System.Configuration.ConfigurationManager.AppSettings["DataBase"];
            SqlParameter[] sqlparams = new SqlParameter[]
            {
                new SqlParameter("@Contact", contact),
                new SqlParameter("@TargetSysName", name),
                new SqlParameter("@Status", statusVal),
                new SqlParameter("@TargetType", targetTypeVal),
                new SqlParameter("@baseDB", baseDB),
                new SqlParameter("@Order", order),
                new SqlParameter("@Sort", sort),
                new SqlParameter("@Rows", pageSize),
                new SqlParameter("@PageNum", pageIndex)
            };
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            ETL_Target_ViewConfigBLL tarViewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            IList<EtlTargetSystemInfo> list = bll.GetList(sqlparams);//列表数据集
            int count = 0;//总记录数
            int count2 = 0;
            if (list.Count > 0)
                count = list[0].Count;
            foreach (EtlTargetSystemInfo tarModel in list)
            {
                var viewList = tarViewConfigBLL.GetViewConfigById(tarModel.GUID, sort, true, 1, 100, out count2);

                int stepStatus = 0;
                if (viewList.Count > 0)
                    stepStatus = 2;
                foreach (ETL_Target_ViewConfigInfo viewModel in viewList)
                {
                    ETL_Task_List_History etlStepModel = sourceViewConfigBLL.GetStepStatus(viewModel.Guid);
                    if (etlStepModel.TaskStatus != 2)
                    {
                        stepStatus = etlStepModel.TaskStatus;
                        break;
                    }
                }
                tarModel.ETLStatus = stepStatus;
            }

            var jsonDict = new Dictionary<String, Object> { { "total", count }, { "rows", list } };
            return Json(jsonDict);
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public ActionResult EtlTargetDelete(string systemID, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL targetSystemBLL = new EtlTargetSystemBLL(loginInfos);
            EtlSourceSystemBLL sourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
            var targetSystemInfo = targetSystemBLL.GetInfoById(systemID);
            if (string.IsNullOrEmpty(targetSystemInfo.GUID))
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "要删除的对接系统不存在";
                jsonResponse.Data = "ERROR";
                return Json(jsonResponse);
            }
            string result = targetSystemBLL.Delete(systemID, targetSystemInfo.TargetSysCode);
            // 删除联系人信息
            sourceSystemBLL.DeleteContactBySysID(systemID, 2);
            // 删除配置的数据库
            sourceSystemBLL.DeleteDbConfigBySysID(systemID);
            if (result == "1")
            {
                jsonResponse.Success = true;
                jsonResponse.Message = "删除成功。";
                new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.删除, OperationStatus.成功, "删除对接系统信息操作,操作成功,ID=" + systemID,
                    Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
            }
            else
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "删除失败," + result;
            }
            return Json(jsonResponse);

        }

        #endregion

        #region EtlTargetAdd

        public ActionResult EtlTargetAdd(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            EtlTargetSystemInfo model = new EtlTargetSystemInfo();
            if (!string.IsNullOrEmpty(systemID))
            {
                model = bll.GetInfoById(systemID);
                model.Connstr = encrypCode.Decrypto(model.Connstr);
            }
            return View(model);
        }

        /// <summary>
        /// 更新对接系统
        /// </summary>
        /// <param name="model">提交的系统信息</param>
        /// <returns></returns>
        public ActionResult EtlTargetUpdate(EtlTargetSystemInfo model, FormCollection collection, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            string DBType = pbll.GetDBInfo(dbName).DBType.ToString();
            int resultCount = 0;
            EtlTargetSystemBLL targetSystemBLL = new EtlTargetSystemBLL(loginInfos);
            EtlSourceSystemBLL sourcesystemBll = new EtlSourceSystemBLL(loginInfos);
            try
            {
                var connList = GetDatabaseConnections(collection);
                var contacts = GetContacts(collection);

                if (string.IsNullOrEmpty(model.TargetDBCode))
                    model.TargetDBCode = "";
                if (connList.Count > 0)
                    model.Connstr = connList[0].DbConnectionStr;

                if (!string.IsNullOrEmpty(model.GUID))//更新信息
                {
                    model.DBType = DBType;
                    resultCount = targetSystemBLL.Update2(model);
                    if (resultCount == 1)
                    {
                        new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新,
                            OperationStatus.成功, "更新对接系统信息操作,操作成功,ID=" + model.GUID, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
                    }
                }
                else//添加信息
                {
                    string tguid = Guid.NewGuid().ToString();
                    model.TargetSysName = model.TargetSysCode = model.FixName + model.TargetSysName;
                    model.GUID = tguid;
                    model.DB_PWD = Guid.NewGuid().ToString();
                    model.DB_UserID = model.TargetSysCode;
                    model.DB_UserID_ForExternal = model.DB_UserID + "_External";
                    model.DB_PWD_ForExternal = targetSystemBLL.GetPwdFromDataBase();
                    resultCount = targetSystemBLL.Insert(model);//添加系统信息
                    new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.添加,
                            OperationStatus.成功, "添加对接系统信息操作,操作成功,ID=" + model.GUID, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
                }
                if (resultCount == 1)
                {
                    #region [sp_addlogin]添加平台数据库登录信息
                    //targetSystemBLL.InsertAddlogin(model, model.DB_PWD);
                    //targetSystemBLL.InsertAdduser(model);
                    //#endregion

                    //#region [sp_addbasegrant]初始化数据库用户权限
                    //targetSystemBLL.InsertAddbasegrant(model);
                    #endregion

                    #region 添加对接系统回调信息
                    //EtlSourceSystemBLL etlSourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
                    //SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@SysID", model.GUID) };
                    //etlSourceSystemBLL.delCBParas(sqlparams);
                    //sqlparams = new SqlParameter[]
                    //    {
                    //        new SqlParameter("@CallBackType", model.CallBackType),
                    //        new SqlParameter("@CallBackName", model.CallBackName),
                    //        new SqlParameter("@GUID", Guid.NewGuid()),
                    //        new SqlParameter("@SysID", model.GUID),
                    //        new SqlParameter("@SysType", 1),
                    //        new SqlParameter("@CreateUser","superadmin"),
                    //        new SqlParameter("@CreateTime", System.DateTime.Now)
                    //    };
                    //etlSourceSystemBLL.InsertCallBack(sqlparams);
                    #endregion

                    #region 连接字符串
                    IList<ETLDbConfig> oldDbConfigList = sourcesystemBll.GetDbConfigBySysID(model.GUID);
                    int dbSortID = 1;
                    for (int i = 0; i < oldDbConfigList.Count; i++)
                    {
                        var oldDbConfigInfo = oldDbConfigList[i];
                        if (i < connList.Count)
                        {
                            var dbConfigInfo = connList[i];
                            oldDbConfigInfo.DbSortID = dbSortID++;
                            oldDbConfigInfo.DbDataSource = dbConfigInfo.DbDataSource;
                            oldDbConfigInfo.DbDatabase = dbConfigInfo.DbDatabase;
                            oldDbConfigInfo.DbUserID = dbConfigInfo.DbUserID;
                            oldDbConfigInfo.DbPassword = dbConfigInfo.DbPassword;
                            oldDbConfigInfo.DbPort = dbConfigInfo.DbPort;
                            oldDbConfigInfo.DbSID = dbConfigInfo.DbSID;
                            oldDbConfigInfo.DbConnectionStr = dbConfigInfo.DbConnectionStr;
                            oldDbConfigInfo.UpdateUser = loginInfos.Dbuser;
                            // 更新
                            sourcesystemBll.UpDateDbConfig(oldDbConfigInfo);
                        }
                        else
                        {
                            // 删除:多余的删除
                            oldDbConfigInfo.DeleteUser = loginInfos.Dbuser;
                            sourcesystemBll.DeleteDbConfig(oldDbConfigInfo);
                        }
                    }
                    for (int i = oldDbConfigList.Count; i < connList.Count; i++)
                    {
                        var dbConfigInfo = connList[i];
                        dbConfigInfo.GUID = Guid.NewGuid().ToString();
                        dbConfigInfo.DbSortID = dbSortID++;
                        dbConfigInfo.SysID = model.GUID;
                        dbConfigInfo.DbType = model.TargetType;
                        dbConfigInfo.CreateUser = model.CreateUser ?? loginInfos.Dbuser;
                        sourcesystemBll.AddDbConfig(dbConfigInfo);
                    }

                    #endregion

                    #region 联系人信息
                    SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",model.GUID)
                };
                    var oldContactsList = sourcesystemBll.GetEtlSourceUserList(cmdParms);
                    // 先处理已经存在的
                    foreach (var oldContactInfo in oldContactsList)
                    {
                        if (!contacts.Contains(oldContactInfo.Guid))// 删除联系人
                        {
                            sourcesystemBll.DeleteContact(oldContactInfo.Guid, model.GUID);
                        }
                    }
                    foreach (string contact in contacts)
                    {
                        string uid = contact;
                        bool isHas = false;
                        if (!string.IsNullOrEmpty(uid))
                        {
                            foreach (var oldContactInfo in oldContactsList)
                            {
                                if (oldContactInfo.Guid.Equals(uid, StringComparison.CurrentCultureIgnoreCase))
                                {
                                    isHas = true;
                                    break;
                                }
                            }
                            if (isHas == false)
                            {
                                // 不存在，增加新的联系人
                                EtlContactInfo contactInfo = new EtlContactInfo()
                                {
                                    GUID = Guid.NewGuid().ToString(),
                                    UserID = uid,
                                    SysID = model.GUID,
                                    SysType = 2
                                };
                                sourcesystemBll.AddContact(contactInfo);
                            }
                        }
                    }
                    #endregion
                }
                if (resultCount == 1)
                {
                    jsonResponse.Success = true;
                    jsonResponse.Message = "保存成功";
                    jsonResponse.Data = "OK";
                }
                else if (resultCount == 2)
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "系统名称已存在!请使用其他系统名称。";
                    jsonResponse.Data = resultCount.ToString();
                }
                else if (resultCount == 3)
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "由系统名称创建的数据库登录名已存在!请使用其他系统名称。";
                    jsonResponse.Data = resultCount.ToString();
                }
                else
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "保存失败";
                    jsonResponse.Data = resultCount.ToString();
                }
                return Json(jsonResponse);
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "保存失败!" + ex.Message;

                return Json(jsonResponse);
            }
        }

        /// <summary>
        /// 取对接系统连接字符串
        /// </summary>
        /// <param name="systemID"></param>
        /// <returns></returns>
        public JsonResult GetTargetDbConfigList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            systemID = string.IsNullOrEmpty(systemID) ? "" : systemID;
            IList<ETLDbConfig> list = bll.GetDbConfigBySysID(systemID);
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            List<ETLDbInfo> dbinfo = new List<ETLDbInfo>();
            foreach (ETLDbConfig db in list)
            {
                ETLDbInfo info = new ETLDbInfo()
                {
                    GUID = db.GUID,
                    Host = db.DbDataSource,
                    Server = db.DbDatabase,
                    User = db.DbUserID,
                    Pass = encrypCode.Decrypto(db.DbPassword),
                    Part = db.DbPort.ToString(),
                    SID = db.DbSID,
                    FileUrl = db.DbDataSource,
                    FileUser = db.DbUserID,
                    FilePass = encrypCode.Decrypto(db.DbPassword)
                };
                dbinfo.Add(info);
            }
            jsonDict.Add("total", dbinfo.Count);
            jsonDict.Add("rows", dbinfo);
            return Json(jsonDict);
        }

        public JsonResult GetTargetUserList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            systemID = string.IsNullOrEmpty(systemID) ? "" : systemID;
            IList<SysUserInfo> list = bll.GetEtlSourceUserList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            return Json(list);
        }

        #endregion

        #region EditDB

        public ActionResult EditDB()
        {
            return View();
        }

        #endregion

        #region 添加模型
        /// <summary>
        /// 添加模型页面
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public ActionResult EtlTargetViewConfig(string dbName)
        {
            PageRole role = new PageRole();
            role.role = SysPublicBLL.GetUserRole(Request.Cookies["MDM_UserName"].Value, dbName);
            return View(role);
        }
        /// <summary>
        /// 已保存模型列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTargetViewConfigList()
        {
            string dbName = Request.Params["dbName"];
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            if (string.IsNullOrEmpty(sort))
                sort = "CreateTime";
            if (string.IsNullOrEmpty(order))
                sort = "desc";
            int rows = int.Parse(Request.Params["rows"]);
            int page = int.Parse(Request.Params["page"]);
            string systemID = Request.Params["systemID"];
            //列表数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;

            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            DBDeploy dbInfo = pbll.GetDBInfo(dbName);
            int totalCount = 0;
            ETL_Target_ViewConfigBLL tarViewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            SqlParameter[] para = new SqlParameter[] { 
             new SqlParameter("@systemID",systemID) 
            ,new SqlParameter("@rows",rows)
            ,new SqlParameter("@page",page)
            };
            List<ETL_Target_ViewConfigInfo> list = tarViewConfigBLL.GetViewConfigList(para, sort, order, out totalCount);
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);

            foreach (ETL_Target_ViewConfigInfo viewModel in list)
            {
                if (viewModel.Type == 1)
                {
                    viewModel.MDP_ViewName = string.Format("<img src='/images/tree_folder.gif' /><a href='#' onclick='viewModel(\"{0}\");'>{1}</a>", viewModel.Guid, viewModel.MDP_ViewName);
                    int etlStatus = 2;
                    List<ETL_Target_ViewConfigInfo> viewConfiglist = viewConfigBll.GetViewConfigBySysIdANDGroupID(systemID, viewModel.Guid, rows, page);
                    if (viewConfiglist.Count == 0)
                        etlStatus = 1;
                    foreach (ETL_Target_ViewConfigInfo model in viewConfiglist)
                    {
                        ETL_Task_List_History etlStepModel = sourceViewConfigBLL.GetStepStatus(model.Guid);
                        if (etlStepModel.TaskStatus != 2)
                        {
                            etlStatus = etlStepModel.TaskStatus;
                            break;
                        }
                    }
                    viewModel.ETLStatus = etlStatus;
                }
                else
                {
                    if (dbInfo.DBType == 2 || viewModel.ConfigStatus == 0)//大数据或未配置
                        viewModel.MDP_ViewName = "<img src='/images/tree_file.gif' />" + viewModel.MDP_ViewName;
                    else
                        viewModel.MDP_ViewName = "<img src='/images/tree_file.gif' /><a href='#' onclick='openViewModelData(\"" + viewModel.Guid + "\")'>" + viewModel.MDP_ViewName + "</a>";
                }
            }
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", totalCount);
            jsonDict.Add("rows", list);
            return Json(jsonDict);
        }

        public ActionResult GetViewList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int rows = Convert.ToInt32(Request.Params["rows"]);
            int page = Convert.ToInt32(Request.Params["page"]);

            DictionaryBLL bll = new DictionaryBLL(loginInfos);
            EtlTargetSystemBLL etlBll = new EtlTargetSystemBLL(loginInfos);
            DataTable dt = etlBll.TargetSystemGetViewList();
            IList<ViewTemp> list = Common.DBConvert.DataTableToList<ViewTemp>(dt);
            SqlParameter[] sqlparams = null;
            foreach (var item in list)
            {
                sqlparams = new SqlParameter[]
                {
                    new SqlParameter("@TargetSystemID",systemID), 
                    new SqlParameter("@MDP_ViewSchema", item.SchemaName),
                    new SqlParameter("@MDP_ViewName", item.TableName)
                };
                dt = etlBll.GetNumTargetViewByST(sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    item.ID = dt.Rows[0][0].ToString();
                    item.IsConfigured = 1;
                }
                else
                {
                    item.ID = "";
                    item.IsConfigured = 0;
                }
                item.SchemaCnName = bll.GetNameByKeyNameAndValue("ModelType", item.SchemaName);
            }
            int count = list.Count;//总记录数
            list = list.Skip((page - 1) * rows).Take(rows).ToList();
            var jsonDict = new Dictionary<String, Object> { { "total", count }, { "rows", list } };
            return Json(jsonDict);
        }
        public ActionResult TargetViewConfigList()
        {
            return View();
        }
        public ActionResult GetAllTargetViewConfigList()
        {
            string dbName = Request.Params["dbName"];

            string ExtIDs = Request.Params["ExtIDs"] ?? "";
            string tableName = Request.Params["TableName"] ?? "";
            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            if (string.IsNullOrEmpty(sort))
                sort = "TargetSysName";
            if (string.IsNullOrEmpty(order))
                order = "asc";
            int rows = int.Parse(Request.Params["rows"]);
            int page = int.Parse(Request.Params["page"]);
            if (ExtIDs.Split(',').Length > 1)
            {
                ExtIDs = string.Format("'{0}'", string.Join("','", ExtIDs.Split(',')));
            }
            else
                ExtIDs = "'" + ExtIDs + "'";
            //列表数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int totalCount = 0;
            ETL_Target_ViewConfigBLL tarViewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            List<SqlParameter> para = new List<SqlParameter>();
            para.Add(new SqlParameter("@tableName", tableName));
            para.Add(new SqlParameter("@Rows", rows));
            para.Add(new SqlParameter("@PageNum", page));

            var list = tarViewConfigBLL.GetAllViewConfigById(para, ExtIDs, sort, order, out totalCount);

            foreach (ETL_Target_ViewConfigInfo viewModel in list)
            {

                int stepStatus = 0;
                if (list.Count > 0)
                    stepStatus = 2;
                ETL_Task_List_History etlStepModel = sourceViewConfigBLL.GetStepStatus(viewModel.Guid);
                if (etlStepModel.TaskStatus == 0 || etlStepModel.TaskStatus == 1)
                {
                    stepStatus = etlStepModel.TaskStatus;
                    break;
                }
                viewModel.ETLStatus = stepStatus;
            }
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", totalCount);
            jsonDict.Add("rows", list);
            return Json(jsonDict);
        }
        /// <summary>
        /// 添加模型
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="dbName"></param> 
        /// <returns></returns>
        public ActionResult AddTargetViewConfig(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            string DBType = Request.Params["DBType"];
            string collType = Request.Params["collType"];//集合类型 交集1，全集2
            string tarviewID = Request.Params["tarviewID"];//添加到已存在的视图
            string sourceViewConfig = Request.Params["SourceViewConfig"];//模型id集合
            var list = this.GetSourceViewConfigs(sourceViewConfig, DBType, loginInfos);
            ETL_Target_ViewConfigBLL targetViewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            systemID = string.IsNullOrEmpty(systemID) ? "" : systemID;

            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            var targetSystemInfo = bll.GetSystemInfoById(systemID);
            if (DBType != "2")
            {
                #region 非集合式添加
                int result = 0;
                foreach (var sourceViewConfigInfo in list)
                {
                    sourceViewConfigInfo.TargetSystemID = systemID;
                    sourceViewConfigInfo.CreateTime = DateTime.Now.ToString("yyyy-MM-dd");
                    sourceViewConfigInfo.CollectionType = 0;
                    try
                    {
                        result = targetViewConfigBll.Insert2(sourceViewConfigInfo);//添加系统视图
                        if (result != 1)
                        {
                            targetViewConfigBll.ETL_Target_ViewConfig_RollBack(sourceViewConfigInfo.Guid, systemID);
                            return Json(0);
                        }
                        new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功,
                            "更新对接系统权限配置信息操作,操作成功,ID=" + sourceViewConfigInfo.Guid, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
                        #region
                        //IList<ETL_Target_View_ColumnConfigTrans> viewColumnConfigTrans = new List<ETL_Target_View_ColumnConfigTrans>();

                        //ETL_Target_View_ColumnConfigInfo _model1 = null;
                        //ETL_Target_View_ColumnConfig_WhereConditionInfo _model2 = null;

                        //ETL_Target_View_ColumnConfigBLL vBll = new ETL_Target_View_ColumnConfigBLL(loginInfos);
                        //TargetViewColumnWhereConditionBLL wBll = new TargetViewColumnWhereConditionBLL(loginInfos);
                        //int i = 1;
                        //foreach (var item in viewColumnConfigTrans)
                        //{
                        //    _model1 = new ETL_Target_View_ColumnConfigInfo()
                        //    {
                        //        Guid = Guid.NewGuid().ToString(),
                        //        ViewID = sourceViewConfigInfo.Guid,
                        //        ColumnOrderID = i,
                        //        ColumnName = item.ColumnName,
                        //        ColumnChnName = item.ColumnChnName,
                        //        ColumnOrderTypeOrderBy = item.ColumnOrderType_OrderBy,
                        //        ColumnOrderIDOrderBy = item.ColumnOrderID_OrderBy,
                        //        ColumnAggregate = item.ColumnAggregate,
                        //        ColumnIsGroup = string.IsNullOrEmpty(item.ColumnAggregate) ? 0 : 1,
                        //        FixedValue = item.FixedValue
                        //    };
                        //    result = vBll.Insert(_model1);//添加系统视图字段
                        //    if (result != 1)
                        //    {
                        //        targetViewConfigBll.ETL_Target_ViewConfig_RollBack(sourceViewConfigInfo.Guid, systemID);
                        //        return Json(0);
                        //    }

                        //    if (!string.IsNullOrEmpty(item.ColumnOperator))
                        //    {
                        //        _model2 = new ETL_Target_View_ColumnConfig_WhereConditionInfo()
                        //        {
                        //            Guid = Guid.NewGuid().ToString(),
                        //            ColumnID = _model1.Guid,
                        //            ColumnOperator = item.ColumnOperator,
                        //            ColumnValue = item.ColumnValue
                        //        };
                        //        result = wBll.Insert(_model2);
                        //        if (result != 1)
                        //        {
                        //            targetViewConfigBll.ETL_Target_ViewConfig_RollBack(sourceViewConfigInfo.Guid, systemID);
                        //            return Json(0);
                        //        }
                        //    }
                        //    i++;
                        //}
                        #endregion
                    }
                    catch (Exception ex)
                    {
                        targetViewConfigBll.ETL_Target_ViewConfig_RollBack(sourceViewConfigInfo.Guid, systemID);
                        return Json(ex.Message);
                    }

                }
                #endregion
            }
            else if (string.IsNullOrEmpty(collType) && string.IsNullOrEmpty(tarviewID))
                collType = "2";

            if (DBType == "2")
            {
                #region 大数据下集合式添加
                string viewID = tarviewID;
                try
                {
                    ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);

                    if (!string.IsNullOrEmpty(tarviewID))//添加到系统视图
                    {
                        columnConfigBLL.InsertViewMapping(viewID, sourceViewConfig);
                    }
                    else
                    {
                        var sourceViewConfigInfo = list[0];
                        viewID = sourceViewConfigInfo.Guid;
                        sourceViewConfigInfo.TargetSystemID = systemID;
                        sourceViewConfigInfo.CollectionType = int.Parse(collType);
                        sourceViewConfigInfo.CreateTime = DateTime.Now.ToString("yyyy-MM-dd");
                        string mvID = sourceViewConfigInfo.ModelViewID;
                        sourceViewConfigInfo.ModelViewID = "";
                        sourceViewConfigInfo.ViewDescription = string.Format("{0}张源表{1}.", list.Count, collType == "1" ? "交集" : "全集");
                        int result = targetViewConfigBll.Insert2(sourceViewConfigInfo);//添加系统视图
                        sourceViewConfigInfo.ModelViewID = mvID;
                        if (result != 1)//添加失败 回滚
                        {
                            targetViewConfigBll.ETL_Target_ViewConfig_RollBack(sourceViewConfigInfo.Guid, systemID);
                            return Json(0);
                        }
                        new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功,
                            "更新对接系统权限配置信息操作,操作成功,ID=" + sourceViewConfigInfo.Guid, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");


                        #region 合并字段
                        List<ETL_Target_View_ColumnConfigInfo> tarColumnList = new List<ETL_Target_View_ColumnConfigInfo>();

                        EtlSourceViewColumnConfigBLL etlSourceViewColumnConfigBLL = new EtlSourceViewColumnConfigBLL(loginInfos);
                        List<ETL_Source_View_ColumnConfigInfo> baseColumnList = new List<ETL_Source_View_ColumnConfigInfo>();
                        List<List<ETL_Source_View_ColumnConfigInfo>> sourceColumnLists = new List<List<ETL_Source_View_ColumnConfigInfo>>();
                        List<string> renameList = new List<string>();

                        foreach (var sourceViewInfo in list)//遍历提交的源表
                        {
                            List<ETL_Source_View_ColumnConfigInfo> sourceViewColumnList =
                                etlSourceViewColumnConfigBLL.GetSourceViewColumnConfigByViewID(sourceViewInfo.ModelViewID);//源表的字段列表
                            sourceColumnLists.Add(sourceViewColumnList);
                            if (baseColumnList.Count == 0)
                                baseColumnList = sourceViewColumnList;
                            else
                            {
                                if (collType == "1")//交集
                                {
                                    List<ETL_Source_View_ColumnConfigInfo> tmpColumnList = new List<ETL_Source_View_ColumnConfigInfo>();
                                    foreach (ETL_Source_View_ColumnConfigInfo tbItem in sourceViewColumnList)
                                    {
                                        if (baseColumnList.FindIndex(o => { return o.ColumnName.ToLower() == tbItem.ColumnName.ToLower(); }) > -1)
                                        {
                                            tmpColumnList.Add(tbItem);
                                        }
                                    }
                                    baseColumnList = tmpColumnList;//赋值交集后的字段集合
                                }
                                else //全集
                                {
                                    List<ETL_Source_View_ColumnConfigInfo> tmpColumnList = new List<ETL_Source_View_ColumnConfigInfo>();
                                    foreach (ETL_Source_View_ColumnConfigInfo tbItem in sourceViewColumnList)
                                    {
                                        int baseIndex = baseColumnList.FindIndex(o => { return o.ColumnName.ToLower() == tbItem.ColumnName.ToLower(); });
                                        if (baseIndex == -1)//添加字段
                                        {
                                            tmpColumnList.Add(tbItem);
                                        }
                                        else
                                        {
                                            if (baseColumnList[baseIndex].ColumnType != tbItem.ColumnType)//相同字段名称，不同字段类型，将名称重命名后添加到集合
                                            {
                                                renameList.Add(tbItem.ColumnName.ToLower());
                                                int count = renameList.Count(o => { return o == tbItem.ColumnName.ToLower(); });
                                                tbItem.ColumnName = string.Format("{0}_{1}", tbItem.ColumnName, count);
                                                tmpColumnList.Add(tbItem);
                                            }
                                        }
                                    }
                                    baseColumnList.AddRange(tmpColumnList);
                                }
                            }
                        }
                        #endregion
                        List<string> sourceViewIDs = new List<string>();
                        #region 保存集合内所有字段
                        int order = 1;
                        foreach (ETL_Source_View_ColumnConfigInfo tbItem in baseColumnList)//保存集合内所有字段
                        {
                            bool add = true;

                            if (add)
                            {
                                ETL_Target_View_ColumnConfigInfo columnConfigInfo = new ETL_Target_View_ColumnConfigInfo()
                                {
                                    ViewID = viewID,
                                    Guid = Guid.NewGuid().ToString(),
                                    ColumnOrderID = order,
                                    ColumnName = tbItem.ColumnName,
                                    ColumnType = tbItem.ColumnType,
                                    ColumnLength = tbItem.ColumnLength,
                                    ColumnPrecision = tbItem.ColumnPrecision,
                                    ColumnScale = tbItem.ColumnScale,
                                    ColumnChnName = tbItem.ColumnCNName,
                                    ColumnOrderTypeOrderBy = "",
                                    ColumnOrderIDOrderBy = 0,
                                    ColumnAggregate = "",
                                    ColumnIsGroup = 0,
                                    FixedValue = "",
                                    WhereCondition = ""
                                };
                                order++;
                                tarColumnList.Add(columnConfigInfo);
                                columnConfigBLL.InsertColumnConfig(columnConfigInfo);
                                //添加到字段映射表
                                foreach (var sourceColList in sourceColumnLists)
                                {
                                    if (sourceColList.Count > 0)
                                    {
                                        ETL_Source_View_ColumnConfigInfo sourceColInfo = sourceColList.Find(o =>
                                        { return o.ColumnName.ToLower() == tbItem.ColumnName.ToLower() && o.ColumnType == tbItem.ColumnType; });
                                        if (sourceColInfo != null)
                                        {
                                            ViewColumnMapping maping = new ViewColumnMapping();
                                            maping.ViewID = columnConfigInfo.Guid;
                                            maping.SourceViewID = sourceColInfo.Guid;
                                            columnConfigBLL.InsertViewColumnMapping(maping);
                                            if (sourceViewIDs.IndexOf(sourceColInfo.ViewID) == -1)
                                                sourceViewIDs.Add(sourceColInfo.ViewID);
                                        }
                                    }
                                }
                            }
                        }
                        #endregion
                        //保存视图映射
                        if (sourceViewIDs.Count > 0)
                            columnConfigBLL.InsertViewMapping(viewID, string.Join(",", sourceViewIDs.ToArray()));
                    }
                }
                catch (Exception ex)
                {
                    targetViewConfigBll.ETL_Target_ViewConfig_RollBack(viewID, systemID);
                    return Json(ex.Message);
                }
                #endregion
            }
            return Json(1);
        }
        /// <summary>
        /// 取消配置
        /// </summary> 
        /// <returns></returns>
        public ActionResult DeleteTargetViewColumns(string systemID, string viewID, string dbName, FormCollection collection)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);

            var viewConfigInfo = viewConfigBLL.GetTargetViewConfigInfoByGuid(viewID);
            if (viewConfigInfo == null)
            {
                return Json(new { Success = false, Message = "模型数据不存在，不能删除" });
            }
            try
            {
                #region 更新字段数据
                if (!string.IsNullOrEmpty(viewConfigInfo.ModelViewID))
                {
                    var columnList = columnConfigBLL.GetTargetViewColumnConfigByViewID(viewID, 0);
                    // 删除字段数据
                    foreach (var columnItem in columnList)
                    {
                        // 删除配置列信息及 Where 条件信息
                        columnConfigBLL.DeleteColumnANDWhereConditionByColumnID(columnItem.Guid);
                    }
                }
                viewConfigBLL.UpdateConfigStatus(viewID);
                #endregion
                new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功,
                    "取消配置信息操作,操作成功,viewID=" + viewID, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
            }
            catch (Exception ex)
            {
                return Json(new { Success = true, Message = ex.Message });
            }
            return Json(new { Success = true, Message = "取消成功" });
        }
        /// <summary>
        /// 删除配置的模型
        /// </summary> 
        /// <returns></returns>
        public ActionResult DeleteTargetView(string systemID, string viewID, string dbName, FormCollection collection)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);

            var columnList = viewConfigBLL.Delete2(viewID);

            return Json(new { Success = true, Message = "删除成功" });
        }
        /// <summary>
        /// 移除视图映射
        /// </summary>
        /// <returns></returns>
        public ActionResult RemoveViewMapping()
        {
            string dbName = Request.Params["dbName"];
            string viewID = Request.Params["viewID"];
            string sourceViewIDs = Request.Params["sourceViewIDs"];
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            try
            {
                //移除视图内的源系统表
                bool columnList = columnConfigBLL.RemoveViewMapping(viewID, sourceViewIDs);
                return Json(new { Success = true, Message = "删除成功" });
            }
            catch (Exception ex)
            {
                return Json(new { Success = true, Message = ex.Message });
            }
        }
        #endregion

        #region 配置字段
        public ActionResult SourceColumnList()
        {
            return View();
        }
        public ActionResult ViewMappingData()
        {
            return View();
        }
        /// <summary>
        /// 取源系统表字段列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetSourceColumnList()
        {
            string ViewID = Request.Params["ViewID"];
            string tarColID = Request.Params["tarColID"];
            string InIDs = Request.Params["InIDs"] ?? "''";
            string ExtIDs = Request.Params["ExtIDs"] ?? "''";
            string columnName = Request.Params["columnName"];
            string tableName = Request.Params["tableName"];
            string sourceSysName = Request.Params["sourceSysName"];
            string dbName = Request.Params["dbName"];
            int rows = int.Parse(Request.Params["rows"]);
            int page = int.Parse(Request.Params["page"]);
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL ViewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            SqlParameter[] par = new SqlParameter[]{
            new SqlParameter("@SourceSysName",sourceSysName),
            new SqlParameter("@tableName",tableName),
            new SqlParameter("@columnName",columnName),
            new SqlParameter("@tarColID",tarColID),
            new SqlParameter("@ViewID",ViewID), 
            new SqlParameter("@Rows",rows),
            new SqlParameter("@PageNum",page)
            };
            int count = 0;
            var result = ViewConfigBLL.GetSourceColumnList(par, InIDs, ExtIDs, tarColID);
            if (result.Count > 0)
                count = result[0].Count;
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", count);
            jsonDict.Add("rows", result);
            return Json(jsonDict);
        }
        /// <summary>
        /// 配置字段页面
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public ActionResult EtlTargetColumnConfig(string systemID, string viewID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL bll = new ETL_Target_ViewConfigBLL(loginInfos);
            EtlTargetSystemBLL sysbll = new EtlTargetSystemBLL(loginInfos);
            var sysInfo = sysbll.GetInfoById(systemID);
            viewID = string.IsNullOrEmpty(viewID) ? "" : viewID;
            var viewInfo = bll.GetTargetViewConfigInfoByGuid(viewID);
            viewInfo.Type = sysInfo.TargetType;
            string bigDataTagValue = bll.BigDataTagValue(viewInfo.ModelViewID);
            if (!string.IsNullOrEmpty(bigDataTagValue))
                viewInfo.ViewDescription = bigDataTagValue;
            return View(viewInfo);
        }
        /// <summary>
        /// 取模型字段信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTargetColumns()
        {
            #region 参数变量
            string viewName = Request.Params["viewName"];
            string viewID = Request.Params["viewID"];
            string reload = Request.Params["reload"];
            string Upper = Request.Params["Upper"];
            string dbName = Request.Params["dbName"];
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL publicBll = new SysPublicBLL(loginInfos);
            DBDeploy dbInfo = publicBll.GetDBInfo(dbName);
            ETL_Target_ViewConfigBLL viewBll = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnBll = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            TargetViewColumnWhereConditionBLL conditionBll = new TargetViewColumnWhereConditionBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            ModelConfigBLL modelBll = new ModelConfigBLL(loginInfos);
            TableInfoBLL tableBll = new TableInfoBLL(loginInfos);
            #endregion

            DataTable dtMDP = new DataTable();
            IList<TableInfo> modelColumnNames = new List<TableInfo>();
            var viewInfo = viewBll.GetTargetViewConfigInfoByGuid(viewID);
            //解析模型名称
            if (dbInfo.DBType == 1) //普通实例
            {
                if (viewInfo != null)
                {
                    //var modelInfo = modelBll.GetItem(viewInfo.ModelViewID);
                    dtMDP = publicBll.GetTableInfoListMDP(viewInfo.ModelViewID);
                    //加载列
                    modelColumnNames.Add(new TableInfo() { ColumnName = "MDP_GUID" });
                    foreach (string cn in dtMDP.MapToStringList("ColumnName"))
                    {
                        modelColumnNames.Add(new TableInfo() { ColumnName = cn });
                    }
                    modelColumnNames.Add(new TableInfo() { ColumnName = "MDP_BatchTime" });
                }
            }
            else
            {
                //ETL_Source_ViewConfigInfo sourModle = sourceViewConfigBLL.GetInfoById(viewInfo.ModelViewID);
                modelColumnNames = tableBll.GetStagingTableInfo(viewInfo.ModelViewID);

            }
            #region 加载已配置的对接系统列信息
            //加载已配置的对接系统列信息 
            List<ETL_Target_View_ColumnConfigInfo> tarColumnList = new List<ETL_Target_View_ColumnConfigInfo>();
            if (reload == "reLoad")//取所有
                tarColumnList = columnBll.GetTargetViewColumnConfigByViewID(viewID, 2);
            else
                tarColumnList = columnBll.GetTargetViewColumnConfigByViewID(viewID, 0);

            IList<ColumnModel> result = new List<ColumnModel>();
            foreach (ETL_Target_View_ColumnConfigInfo _temp in tarColumnList)
            {
                string tarColumnName = _temp.ColumnName;
                if (Upper == "Upper")
                {
                    tarColumnName = tarColumnName.ToUpper();
                }
                var conditionList = conditionBll.GetColumnWhereConditionConfigByColumnID(_temp.Guid);
                string typevalue = _temp.ColumnType;
                typevalue = SysPublicBLL.FomatColumnType(_temp.ColumnType.ToLower(), _temp.ColumnLength.ToString(), _temp.ColumnPrecision, _temp.ColumnScale, "");

                ColumnModel columnModel = new ColumnModel()
                {
                    Checked = true,
                    GUID = _temp.Guid,
                    ColumnName = tarColumnName,
                    ColumnType = typevalue,
                    ColumnAggregate = _temp.ColumnAggregate,
                    FixedValue = _temp.FixedValue,
                    ColumnIsGroup = _temp.ColumnIsGroup,
                    ColumnChnName = _temp.ColumnChnName,
                    ColumnOrderType_OrderBy = _temp.ColumnOrderTypeOrderBy,
                    ColumnOrderID_OrderBy = _temp.ColumnOrderIDOrderBy,
                    WhereCondition = _temp.WhereCondition,
                    IsSaved = 1,
                    column_id = -1
                };
                if (conditionList.Count > 0)
                {
                    foreach (var conditionItem in conditionList)
                    {
                        columnModel.ColumnConditions.Add(new ColumnConditionModel()
                        {
                            ColumnOperator = conditionItem.ColumnOperator,
                            ColumnValue = conditionItem.ColumnValue.ToString(),
                            IsValue = conditionItem.IsValue
                        });
                    }
                }
                result.Add(columnModel);
            }
            bool ck = false;
            if (tarColumnList.Count == 0)
                ck = true;
            foreach (TableInfo modelColumnName in modelColumnNames)
            {
                var _temp = tarColumnList.Where(p => p.ColumnName.ToLower().Equals(modelColumnName.ColumnName.ToLower()) || p.ColumnName.ToLower().Contains("[" + modelColumnName.ColumnName.ToLower() + "]")).FirstOrDefault();
                if (_temp == null)//未保存
                {
                    if (reload == "reLoad" || tarColumnList.Count == 0)
                    {
                        string tarColumnName = modelColumnName.ColumnName;
                        if (Upper == "Upper")
                        {
                            tarColumnName = tarColumnName.ToUpper();
                        }
                        result.Add(new ColumnModel()
                        {
                            Checked = ck,
                            column_id = -1,
                            ColumnName = tarColumnName,
                            ColumnType = modelColumnName.ColumnType,
                            ColumnChnName = tarColumnName,
                            IsSaved = 0,
                            FixedValue = string.Empty,
                            WhereCondition = string.Empty
                        });
                    }
                }
            }
            #endregion
            result = result.OrderBy(p => p.column_id).ToList();

            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", result.Count);
            jsonDict.Add("rows", result);
            return Json(jsonDict);
        }
        /// <summary>
        /// 取字段映射的源列表
        /// </summary>
        /// <returns></returns>
        public string GetMappingList()
        {
            string tarcolID = Request.Params["tarcolID"];
            string dbName = Request.Params["dbName"];

            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL bll = new SysPublicBLL(loginInfos);
            ETL_Target_ViewConfigBLL etlBll = new ETL_Target_ViewConfigBLL(loginInfos);
            return etlBll.GetMappingList(tarcolID);
        }
        /// <summary>
        /// 取模型映射字段关系
        /// </summary>
        /// <returns></returns>
        public ActionResult GetViewMappingData()
        {
            string systemID = Request.Params["systemID"];
            string viewID = Request.Params["viewID"];
            string dbName = Request.Params["dbName"];

            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL bll = new SysPublicBLL(loginInfos);
            ETL_Target_ViewConfigBLL etlBll = new ETL_Target_ViewConfigBLL(loginInfos);

            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", 1);
            jsonDict.Add("rows", etlBll.GetViewMappingData(viewID));
            return Json(jsonDict);
        }
        public ActionResult GetViewColumns2(string viewName, string viewID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL bll = new SysPublicBLL(loginInfos);
            ETL_Target_ViewConfigBLL etlBll = new ETL_Target_ViewConfigBLL(loginInfos);
            DataTable dtMDP = bll.GetTableInfoListMDP(viewName);
            IList<MDPColumnTrans> dataMDP = dtMDP.MapToList<MDPColumnTrans>();//Common.DBConvert.DataTableToList<MDPColumnTrans>(dtMDP);

            DataTable dtETL = etlBll.Load(viewID);
            IList<ETL_Target_View_ColumnConfigTrans> dataETL = dtETL.MapToList<ETL_Target_View_ColumnConfigTrans>();
            // Common.DBConvert.DataTableToList<ETL_Target_View_ColumnConfigTrans>(dtETL);

            IList<ETL_Target_View_ColumnConfigTrans> result = new List<ETL_Target_View_ColumnConfigTrans>();
            foreach (MDPColumnTrans item in dataMDP)
            {
                ETL_Target_View_ColumnConfigTrans _temp = dataETL.Where(p => p.ColumnName.ToLower().Equals(item.name.ToLower())).FirstOrDefault();
                if (_temp == null)
                {
                    result.Add(new ETL_Target_View_ColumnConfigTrans()
                    {
                        column_id = item.column_id,
                        ColumnName = item.name,
                        IsSaved = 0
                    });
                }
                else
                {
                    result.Add(new ETL_Target_View_ColumnConfigTrans()
                    {
                        ColumnName = item.name,
                        ColumnAggregate = _temp.ColumnAggregate,
                        FixedValue = _temp.FixedValue,
                        ColumnValue = _temp.ColumnValue,
                        ColumnOperator = _temp.ColumnOperator,
                        ColumnIsGroup = _temp.ColumnIsGroup,
                        ColumnChnName = _temp.ColumnChnName,
                        ColumnOrderType_OrderBy = _temp.ColumnOrderType_OrderBy,
                        ColumnOrderID_OrderBy = _temp.ColumnOrderID_OrderBy,
                        IsSaved = 1,
                        column_id = item.column_id
                    });
                }
            }
            result = result.OrderBy(p => p.column_id).ToList();
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", result.Count);
            jsonDict.Add("rows", result);
            return Json(jsonDict);
        }
        /// <summary>
        /// 保存模型字段配置
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewID"></param>
        /// <param name="dbName"></param>
        /// <param name="collection"></param>
        /// <returns></returns>
        public ActionResult SaveTargetViewColumns(string systemID, string viewID, string dbName, FormCollection collection)
        {
            string IsDistinct = Request.Params["IsDistinct"];
            string IsExtract = Request.Params["IsExtract"];
            string ExtractNum = Request.Params["ExtractNum"];
            string whereCondition = collection["whereCondition"];
            string strColumns = collection["strColumns"];
            string Upper = collection["Upper"];
            string MDP_ViewName = Request.Params["MDP_ViewName"];
            string ViewDescription = Request.Params["ViewDescription"];
            string DBType = collection["DBType"];
            IList<ColumnModel> columnModelList = null;

            #region 验证参数
            if (string.IsNullOrEmpty(strColumns))
            {
                return Json(new { Success = false, Message = "列信息为空" });
            }
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            columnModelList = (IList<ColumnModel>)jsSerializer.Deserialize(strColumns, typeof(IList<ColumnModel>));

            if (columnModelList == null || columnModelList.Count == 0)
            {
                return Json(new { Success = false, Message = "列信息为空" });
            }
            if (DBType == "2")
            {
                try
                {
                    var CheckColumnType = (IList<ETL_Source_View_ColumnEdit>)jsSerializer.Deserialize(strColumns, typeof(IList<ETL_Source_View_ColumnEdit>));

                    string ct_msg = SysPublicBLL.CheckType(CheckColumnType);//判断数据类型
                    if (ct_msg != "1")
                    {
                        return Json(new { Success = false, Message = "解析列信息失败:" + ct_msg.Replace("<br />", "\r\n") });
                    }
                }
                catch (Exception ex)
                {
                    return Json(new { Success = false, Message = "解析列信息失败:" + ex.Message });
                }
            }
            #endregion

            #region 读取数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            var viewConfigInfo = viewConfigBLL.GetTargetViewConfigInfoByGuid(viewID);
            if (viewConfigInfo == null)
            {
                return Json(new { Success = false, Message = "模型数据不存在，不能更新" });
            }
            #endregion

            #region 更新数据

            // 更新视图模型数据
            if (string.IsNullOrEmpty(IsExtract))
                IsExtract = "0";
            if (string.IsNullOrEmpty(ExtractNum))
                ExtractNum = "0";
            viewConfigInfo.IsExtract = int.Parse(IsExtract);
            viewConfigInfo.ExtractNum = int.Parse(ExtractNum);
            viewConfigInfo.IsDistinct = int.Parse(IsDistinct);
            viewConfigInfo.WhereCondition = whereCondition;
            if (viewConfigInfo.CollectionType == 1 || viewConfigInfo.CollectionType == 2)
            {
                viewConfigInfo.MDP_ViewName = Request.Params["MDP_ViewName"];
                viewConfigInfo.ViewDescription = Request.Params["ViewDescription"];
            }
            viewConfigBLL.Update(viewConfigInfo);

            #region 更新字段数据
            var columnList = columnConfigBLL.GetTargetViewColumnConfigByViewID(viewID, 2);
            // 先删除多余的字段数据
            foreach (var columnItem in columnList)
            {
                // 从配置的模型列中查找，ColumnName 和 Checked 标记
                var _temp = columnModelList.Where(p => p.ColumnName.ToLower().Equals(columnItem.ColumnName.ToLower()) && p.Checked == true).FirstOrDefault();
                if (_temp == null)
                {
                    // 删除配置列信息及 Where 条件信息
                    columnConfigBLL.DeleteColumnANDWhereConditionByColumnID(columnItem.Guid);
                }
                else
                {
                    // 存在，仅仅删除Where条件信息，保留列信息，后面再补充Where条件信息
                    columnConfigBLL.DeleteWhereConditionByColumnID(columnItem.Guid);
                }
            }
            int i = 1;
            // 添加新增加的字段数据
            foreach (var columnModelItem in columnModelList)
            {
                string columnName = columnModelItem.ColumnName;
                if (Upper == "Upper")
                {
                    columnName = columnName.ToUpper();
                }
                if (columnModelItem.Checked)
                {
                    // 从配置的模型列中查找，ColumnName 和 Checked 标记
                    var _temp = columnList.Where(p => p.ColumnName.ToLower().Equals(columnModelItem.ColumnName.ToLower())).FirstOrDefault();
                    string columnType = columnModelItem.ColumnType;
                    int columnLength = 0;
                    int columnPrecision = 0;
                    int columnScale = 0;

                    #region 解析字段类型
                    string typevalue = "";
                    string[] typeArr = columnType.Replace(")", "").Split('(');
                    typevalue = typeArr[0].ToLower();
                    if (typeArr.Length > 1)
                    {
                        string[] lengthArr = typeArr[1].Split(',');
                        if (lengthArr.Length == 1)
                        {
                            if (lengthArr[0].ToUpper().Equals("MAX"))
                            {
                                columnLength = -1;
                            }
                            else
                            {
                                int.TryParse(lengthArr[0], out columnLength);
                            }
                        }
                        else
                        {
                            if (typevalue.ToLower().Equals("numeric") || typevalue.ToLower().Equals("decimal"))
                            {
                                int.TryParse(lengthArr[0], out columnLength);
                                int.TryParse(lengthArr[0], out columnPrecision);
                                int.TryParse(lengthArr[1], out columnScale);
                            }
                        }
                    }
                    #endregion
                    ETL_Target_View_ColumnConfigInfo columnConfigInfo = new ETL_Target_View_ColumnConfigInfo()
                    {
                        ViewID = viewID,
                        ColumnOrderID = i,
                        ColumnName = columnName,
                        ColumnType = typevalue,
                        ColumnLength = columnLength,
                        ColumnPrecision = columnPrecision,
                        ColumnScale = columnScale,
                        ColumnChnName = columnModelItem.ColumnChnName,
                        ColumnOrderTypeOrderBy = columnModelItem.ColumnOrderType_OrderBy,
                        ColumnOrderIDOrderBy = columnModelItem.ColumnOrderID_OrderBy,
                        ColumnAggregate = columnModelItem.ColumnAggregate,
                        ColumnIsGroup = string.IsNullOrEmpty(columnModelItem.ColumnAggregate) ? 0 : 1,
                        FixedValue = columnModelItem.FixedValue,
                        WhereCondition = columnModelItem.WhereCondition
                    };
                    i++;
                    if (_temp == null)
                    {
                        // 不存在，添加
                        columnConfigInfo.Guid = Guid.NewGuid().ToString();
                        columnConfigBLL.InsertColumnConfig(columnConfigInfo);
                    }
                    else
                    {
                        // 数据库已经存在，直接更新
                        columnConfigInfo.Guid = _temp.Guid;
                        columnConfigBLL.UpdateColumnConfig(columnConfigInfo);
                    }
                    foreach (var conditionModelItem in columnModelItem.ColumnConditions)
                    {
                        var whereConditionInfo = new ETL_Target_View_ColumnConfig_WhereConditionInfo()
                        {
                            Guid = Guid.NewGuid().ToString(),
                            ColumnID = columnConfigInfo.Guid,
                            ColumnOperator = conditionModelItem.ColumnOperator,
                            ColumnValue = conditionModelItem.ColumnValue,
                            IsValue = conditionModelItem.IsValue
                        };
                        columnConfigBLL.InsertWhereCondition(whereConditionInfo);
                    }
                    if (!string.IsNullOrEmpty(columnModelItem.MappingGUID))
                    { //添加字段映射
                        columnConfigBLL.DeleteColumnMaping(columnModelItem.GUID);
                        string[] arrMapping = columnModelItem.MappingGUID.Split(',');
                        foreach (string s in arrMapping)
                        {
                            columnConfigBLL.AddColumnMaping(columnConfigInfo.Guid, s);
                        }
                    }
                }
            }

            #endregion

            #endregion
            new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功,
                "更新模型字段配置信息操作,操作成功,viewID=" + viewID, Request.Cookies["MDM_UserName"] != null ? Request.Cookies["MDM_UserName"].Value : "");
            return Json(new { Success = true, Message = "保存成功" });
        }

        #endregion

        #region ViewModelData

        [HttpGet]
        public ActionResult ViewModelData(string systemID, string viewID, string dbName)
        {
            return View();
        }

        [HttpPost]
        public ActionResult GetModelTableData(string systemID, string viewID, string dbName)
        {

            #region 变量参数
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL publicBll = new SysPublicBLL(loginInfos);

            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            ModelConfigBLL modelBll = new ModelConfigBLL(loginInfos);
            #endregion
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {
                #region 声明变量与参数
                string order = Request.Params["order"];
                if (order == null)
                    order = string.Empty;
                string sort = Request.Params["sort"];
                if (sort == null)
                    sort = "1";
                string query = Request.Params["query"];
                string type = "1";
                List<string> sql = new List<string>();
                IDataParameter[] sqlPara = null;
                if (!string.IsNullOrEmpty(query) && query != "[]")
                {
                    type = "2";
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");

                    sqlPara = new SqlParameter[arrList.Count()];
                    int i = 0;
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        switch (item[1])
                        {
                            case "like":
                            case "not like":
                                sql.Add(string.Format(" {0} {1} '%'+@{0}+'%' ", item[0], item[1]));
                                break;
                            case "is null":
                            case "is NOT null":
                                sql.Add(string.Format(" {0} {1} ", item[0], item[1]));
                                break;
                            case "in":
                            case "not in":
                                sql.Add(string.Format(" {0} {1} (@{0}) ", item[0], item[1]));
                                break;
                            default:
                                sql.Add(string.Format(" {0} {1} @{0} ", item[0], item[1]));// " column=@column"
                                break;
                        }
                        sqlPara[i] = new SqlParameter("@" + item[0], item[2]);
                        i++;
                    }
                }
                #endregion

                #region 读取数据
                var viewConfigInfo = viewConfigBLL.GetTargetViewConfigInfoByGuid(viewID);
                var modelInfo = modelBll.GetItem(viewConfigInfo.ModelViewID);
                string strTableName = string.Format("[{0}].[{1}]", "mdm", modelInfo.ModelTableCode);
                #endregion

                #region 拼接列数据
                var columnList = columnConfigBLL.GetTargetViewColumnConfigByViewID(viewID, 0);
                var columnNameDics = new Dictionary<string, string>();
                foreach (var columnItem in columnList)
                {
                    if (!columnNameDics.ContainsKey(columnItem.ColumnName.ToUpper()))
                    {
                        columnNameDics.Add(columnItem.ColumnName.ToUpper(), columnItem.ColumnChnName);
                    }
                }

                string strColumn = string.Empty;
                foreach (var columnInfo in columnList)
                {
                    strColumn += string.Format("[{0}],", columnInfo.ColumnName);
                }
                if (strColumn.EndsWith(","))
                {
                    strColumn = strColumn.Remove(strColumn.Length - 1, 1);
                }
                if (!string.IsNullOrEmpty(order) && !strColumn.Contains(order))
                {
                    return Json(new { Success = false, Message = "排序的列并不存在于该表中，请刷新后重试" });
                }
                #endregion

                #region 读取本地数据表数据
                var dt = viewConfigBLL.GetModelTableData(viewID, type, order, sort, sql.ToArray(), sqlPara);

                var strDT = this.DataTableToString(SysPublicBLL.GetPagedTable(dt, 1, 13), null, order, sort, "initModelTableData");
                #endregion
                return Json(new { Success = true, Message = "OK", Data = strDT, Head = SysPublicBLL.GetSourceTableHeadData(dt.Columns) });
            }
            catch (Exception ex)
            {
                return Json(new { Success = false, Message = "信息获取失败！" + ex.Message });
            }
        }
        /// <summary>
        /// 对接系统表数据
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns> 
        public ActionResult GetTargetTableData(string systemID, string viewID, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            try
            {
                jsonResponse = CallTargetDate(systemID, viewID, dbName);
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = ex.Message;
            }
            return Json(jsonResponse);
        }
        private JsonResponse<string> CallTargetDate(string systemID, string viewID, string dbName)
        {
            #region 变量参数
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            EtlTargetSystemBLL bll = new EtlTargetSystemBLL(loginInfos);
            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            ModelConfigBLL modelBll = new ModelConfigBLL(loginInfos);
            #endregion
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {
                #region 搜索项配置
                string query = Request.Params["query"];
                List<string> sql = new List<string>();
                IDataParameter[] para = null;
                IDataParameter[] sqlPara = null;
                IDataParameter[] oraPara = null;
                IDataParameter[] myPara = null;
                IDataParameter[] NpgPara = null;
                if (!string.IsNullOrEmpty(query) && query != "[]")
                {
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");

                    sqlPara = new IDataParameter[arrList.Count()];
                    oraPara = new IDataParameter[arrList.Count()];
                    myPara = new IDataParameter[arrList.Count()];
                    NpgPara = new IDataParameter[arrList.Count()];
                    int i = 0;
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        switch (item[1])
                        {
                            case "like":
                            case "not like":
                                sql.Add(string.Format(" {0} {1} '%'+@{0}+'%' ", item[0], item[1]));
                                break;
                            case "is null":
                            case "is NOT null":
                                sql.Add(string.Format(" {0} {1} ", item[0], item[1]));
                                break;
                            case "in":
                            case "not in":
                                sql.Add(string.Format(" {0} {1} (@{0}) ", item[0], item[1]));
                                break;
                            default:
                                sql.Add(string.Format(" {0} {1} @{0} ", item[0], item[1]));// " column=@column"
                                break;
                        }
                        sqlPara[i] = new SqlParameter("@" + item[0], item[2]);
                        oraPara[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(":" + item[0], item[2]);
                        myPara[i] = new MySql.Data.MySqlClient.MySqlParameter("?" + item[0], item[2]);
                        NpgPara[i] = new NpgsqlParameter(":" + item[0], item[2]);
                        i++;
                    }
                }
                string order = Request.Params["order"];
                string sort = Request.Params["sort"];
                if (string.IsNullOrEmpty(sort))
                    sort = "desc";
                if (string.IsNullOrEmpty(order))
                    order = "";

                var targetSysInfo = bll.GetInfoById(systemID);

                EncrypCode encrypCode = new EncrypCode();
                string connstr = encrypCode.Decrypto(targetSysInfo.Connstr);
                string connType = targetSysInfo.TargetType.ToString();//数据库类型

                EtlSourceSystemBLL sourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
                var viewInfo = viewConfigBLL.GetInfoById(viewID);
                string tname = viewInfo.PushName;
                if (string.IsNullOrEmpty(tname))
                    tname = viewInfo.ViewName;
                if (!string.IsNullOrEmpty(viewInfo.PushSchema))
                    tname = string.Format("[{0}].[{1}]", viewInfo.PushSchema, tname);
                else
                    tname = "[" + tname + "]";
                if (connType == "2")
                    para = oraPara;
                else if (connType == "4")
                    para = myPara;
                else if (connType == "8")
                    para = NpgPara;
                else
                    para = sqlPara;
                #endregion
                DataTable dt = sourceSystemBLL.GetSourceTableData(tname, connstr, connType, order, sort, sql.ToArray(), para);

                #region 读取本地数据表数据
                var html = new System.Text.StringBuilder();
                var strDT = this.DataTableToString(dt, null, order, sort, "GetTargetTableData");
                jsonResponse.Success = true;
                jsonResponse.Message = "OK";
                jsonResponse.Data = strDT;
                jsonResponse.Head = SysPublicBLL.GetSourceTableHeadData(dt.Columns);
                #endregion
                return jsonResponse;
            }
            catch (Exception ex)
            {
                string errMsg = ex.Message;
                if (errMsg.IndexOf("对象名") > -1 && errMsg.IndexOf("无效") > -1)
                    errMsg = errMsg.Replace("对象名", "在对接系统中找不到表：").Replace("无效", "，请检查模型推送名称");
                jsonResponse.Success = false;
                jsonResponse.Message = "信息获取失败！" + errMsg;
                jsonResponse.Data = "";
                return jsonResponse;
            }
        }
        /// <summary>
        /// 对比表结构
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public ActionResult GetCmpCloumnData(string systemID, string viewID, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();

            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {

                jsonResponse.Success = true;
                jsonResponse.Message = "";
                jsonResponse.Data = GetSourceTBColumnInfo(systemID, viewID, dbName);
                return Json(jsonResponse);
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "信息获取失败！" + ex.Message;
                jsonResponse.Data = "";
                return Json(jsonResponse);
            }
        }
        /// <summary>
        /// 对比表结构
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public string GetSourceTBColumnInfo(string systemID, string viewID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysPublicBLL publicBll = new SysPublicBLL(loginInfos);
            DBDeploy dbInfo = publicBll.GetDBInfo(dbName);

            ETL_Target_ViewConfigBLL viewConfigBLL = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBLL = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            TableInfoBLL bll = new TableInfoBLL(loginInfos);
            EtlSourceSystemBLL sourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
            EtlTargetSystemBLL tarGetbll = new EtlTargetSystemBLL(loginInfos);
            EtlSourceViewColumnConfigBLL etlSourceViewColumnConfigBLL = new EtlSourceViewColumnConfigBLL(loginInfos);
            #region 本地表结构
            var viewConfigInfo = viewConfigBLL.GetTargetViewConfigInfoByGuid(viewID);

            var columnList = columnConfigBLL.GetTargetViewColumnConfigByViewID(viewID, 0);//已保存视图
            List<TableInfo> tbList = new List<TableInfo>();
            if (dbInfo.DBType == 1)
            {
                IList<ModelViewConfig> mvList = bll.GetModelViewInfo(viewConfigInfo.ModelViewID, "");//取模型扩展字段
                foreach (ModelViewConfig item in mvList)
                {
                    if (columnList.FindIndex(o => { return o.ColumnName.Equals(item.ColumnName, StringComparison.CurrentCultureIgnoreCase); }) > -1)
                    {
                        TableInfo _temp = new TableInfo
                        {
                            GUID = item.GUID,
                            ColumnName = item.ColumnName,
                            Desc = item.ColumnDescription,
                            ColumnType = item.ColumnType,
                            ColumnTypeSource = item.ColumnType,
                            Length = item.ColumnLength,
                            Precision = item.ColumnPrecision,
                            DecimalPlaces = item.ColumnScale,
                            PK = item.ColumnIsBusinessPK.ToString(),
                            IsNull = item.ColumnIsNullable.Trim(),
                            DefaultValue = item.ColumnDefaultValue
                        };
                        tbList.Add(_temp);
                    }
                }
            }
            else
            {
                IList<TableInfo> mvList = bll.GetStagingTableInfo("Staging_db", viewConfigInfo.ViewName);
                foreach (TableInfo item in mvList)
                {
                    if (columnList.FindIndex(o => { return o.ColumnName.Equals(item.ColumnName, StringComparison.CurrentCultureIgnoreCase); }) > -1)
                    {
                        tbList.Add(item);
                    }
                }
            }
            #endregion
            #region 对接表结构
            var targetSysInfo = tarGetbll.GetInfoById(systemID);
            EncrypCode encrypCode = new EncrypCode();
            string connstr = encrypCode.Decrypto(targetSysInfo.Connstr);
            string connType = targetSysInfo.TargetType.ToString();//数据库类型 
            string tname = viewConfigInfo.PushName;
            string tower = viewConfigInfo.PushSchema;
            if (string.IsNullOrEmpty(tname))
                tname = viewConfigInfo.ViewName;
            if (string.IsNullOrEmpty(tower))
                tname = string.Format("[{0}]", tname);
            else
                tname = string.Format("[{0}].[{1}]", tower, tname);

            List<ETL_Source_View_ColumnConfigInfo> allList = new List<ETL_Source_View_ColumnConfigInfo>();
            try
            {
                allList = sourceSystemBLL.GetTableInfoList(tname, connstr, connType, tower);

                var columnPar = new SqlParameter[] { new SqlParameter("@viewName", viewConfigInfo.PushName), };
                if (connType != "3" && connType != "5")
                    allList = etlSourceViewColumnConfigBLL.GetColumnInfoByOutSystem(columnPar, connstr, connType, viewConfigInfo.PushSchema);
            }
            catch (Exception ex) { throw ex; }
            #endregion
            StringBuilder sb = new StringBuilder();
            foreach (TableInfo item in tbList)//匹配字段与字段类型
            {
                //item.ColumnType = FomatColumnType(item.ColumnType.ToLower(), item.ColumnLength.ToString(), item.ColumnPrecision, item.ColumnScale, connType);
                ETL_Source_View_ColumnConfigInfo _temp = allList.Where(p => p.ColumnName.Equals(item.ColumnName, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
                string pk = item.PK;
                string _pk = string.Empty;
                string _ty = string.Empty;
                if (_temp == null)
                {
                    item.ColumnType = string.Format("<font color='#ff0000'>{0}</font>", item.ColumnType);
                    pk = string.Format("<font color='#ff0000'>{0}</font>", pk);
                }
                else
                {
                    _pk = _temp.ColumnIsPK == 1 ? "是" : "否";
                    _ty = SysPublicBLL.FomatColumnType(_temp.ColumnType.ToLower(), _temp.ColumnLength.ToString(), _temp.ColumnPrecision, _temp.ColumnScale, connType);

                    if (_pk != item.PK)
                        pk = string.Format("<font color='#ff0000'>{0}</font>", pk);
                    if (_ty != item.ColumnType)
                        item.ColumnType = string.Format("<font color='#ff0000'>{0}</font>", item.ColumnType);
                }
                sb.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td align='center'>{2}</td><td>{3}</td><td align='center'>{4}</td></tr>",
                    item.ColumnName, item.ColumnType, pk, _ty, _pk));
            }
            foreach (ETL_Source_View_ColumnConfigInfo item in allList)//匹配字段与字段类型
            {
                TableInfo _temp = tbList.Where(p => p.ColumnName.Equals(item.ColumnName, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();

                if (_temp == null)
                {
                    sb.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td align='center'>{2}</td><td><font color='#ff0000'>{3}</font></td><td align='center'><font color='#ff0000'>{4}</font></td></tr>",
                        item.ColumnName, "", "", item.ColumnType, item.ColumnIsPK == 1 ? "是" : "否"));
                }
            }
            return sb.ToString();
        }

        #endregion

        #region SelectTargetList

        public ActionResult SelectTargetList()
        {
            return View();
        }

        public ActionResult GetSelectTargetList()
        {
            string dbName = Request.Params["dbName"];

            string order = Request.Params["order"];
            string sort = Request.Params["sort"];
            if (string.IsNullOrEmpty(sort))
                sort = "CreateTime";
            string rows = Request.Params["rows"];
            string page = Request.Params["page"];
            string targetSysName = Request.Params["targetSysName"];
            string expIDs = Request.Params["ExpIDs"];
            //列表数据
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            int totalCount = 0;
            EtlTargetSystemBLL sourceViewConfigBLL = new EtlTargetSystemBLL(loginInfos);
            var list = sourceViewConfigBLL.GetList(targetSysName, expIDs,
                sort, order.Equals("asc", StringComparison.CurrentCultureIgnoreCase)
                , Convert.ToInt32(page), Convert.ToInt32(rows), out totalCount);
            foreach (var item in list)
            {
                item.CreateTime = Convert.ToDateTime(item.CreateTime).ToString("yyyy-MM-dd");
            }
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", totalCount);
            jsonDict.Add("rows", list);
            return Json(jsonDict);
        }
        #endregion

        #region ViewTargetConfig

        public ActionResult ViewTargetConfig(string systemID, string dbName)
        {
            var html = GetTargetConfigInfo(systemID, dbName);
            ViewBag.ViewConfigHtml = html;
            return View();
        }


        /// <summary>
        /// 权限查看
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public string GetTargetConfigInfo(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            ETL_Target_ViewConfigBLL viewConfigBll = new ETL_Target_ViewConfigBLL(loginInfos);
            ETL_Target_View_ColumnConfigBLL columnConfigBll = new ETL_Target_View_ColumnConfigBLL(loginInfos);
            var html = new System.Text.StringBuilder();

            var par = new SqlParameter[]
                                   {
                                       new SqlParameter("@TargetSystemID",systemID), 
                                   };
            List<ETL_Target_ViewConfigInfo> viewConfigInfos = viewConfigBll.GetViewBySystemId(par);

            foreach (var etlViewConfigInfo in viewConfigInfos)
            {
                var viewColumnPar = new SqlParameter[]
                                    {
                                        new SqlParameter("@ViewID",etlViewConfigInfo.Guid), 
                                    };
                var columnList = columnConfigBll.GetViewColumn(viewColumnPar);
                html.AppendFormat("<tr><td title='{0}'>{0}</td><td>", etlViewConfigInfo.MDP_ViewName);
                foreach (var model in columnList)
                {
                    html.AppendFormat("<div title='{0}({1})'>{0}({1})</div>", model.ColumnChnName, model.ColumnName);
                }
                html.Append("</td>");
                // html.AppendFormat("<td >{0}</td>", etlViewConfigInfo.CreateViewScript);
                html.AppendFormat("<td>{1}</td>", etlViewConfigInfo.CreateTableScript, etlViewConfigInfo.CreateTableScript.Replace("\n", "<BR >"));
                html.AppendFormat("<td >{0}</td>", etlViewConfigInfo.MDPViewSchema);
            }
            return html.ToString();
        }

        #endregion

        #region 公共单元

        private List<ETLDbConfig> GetDatabaseConnections(FormCollection collection)//, int sourceType)
        {
            List<ETLDbConfig> list = new List<ETLDbConfig>();
            var sourceType = Convert.ToInt32(collection["TargetType"]);

            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            ETLDbInfo dbInfo = (ETLDbInfo)jsSerializer.Deserialize(collection["DBs"], typeof(ETLDbInfo));

            var dataSource = dbInfo.Host;
            var database = dbInfo.Server;
            var userID = dbInfo.User;
            var password = dbInfo.Pass;
            string port = dbInfo.Part;
            var sid = dbInfo.SID;
            var strCon = string.Empty;

            switch (sourceType)
            {
                case 1:
                    strCon = string.Format(DatabaseConnStr.SQLSERVER_FRAMAT, dataSource, database, userID, password, port, sid);
                    break;
                case 2:
                    strCon = string.Format(DatabaseConnStr.ORACLE_FRAMAT, dataSource, database, userID, password, port, sid);
                    if (dataSource.IndexOf("|") > -1)
                    {
                        List<string> hostArr = dataSource.Split('|').ToList();
                        List<string> portArr = dbInfo.Part.Split('|').ToList();
                        StringBuilder ora = new StringBuilder();
                        ora.Append("Data Source=(DESCRIPTION=");
                        for (int i = 0; i < hostArr.Count; i++)
                        {
                            ora.Append(string.Format("(ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1}))", hostArr[i], portArr[i]));
                        }
                        ora.Append(string.Format("(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {0})));User Id={1};Password={2};", database, userID, password));
                        strCon = ora.ToString();
                    }
                    break;
                case 4:
                case 8:
                    strCon = string.Format(DatabaseConnStr.MYSQL_FRAMAT, dataSource, database, userID, password, port, sid);
                    break;
                case 3:
                case 5:
                case 6:
                    dataSource = dbInfo.FileUrl;
                    userID = dbInfo.FileUser;
                    password = dbInfo.FilePass;
                    strCon = string.Format(DatabaseConnStr.ACCESS_FRAMAT, dbInfo.FileUrl, dbInfo.FileUser, dbInfo.FilePass);
                    break;
            }
            list.Add(new ETLDbConfig()
            {

                DbDataSource = dataSource,
                DbDatabase = database,
                DbUserID = userID,
                DbPassword = encrypCode.Encrypto(password),
                DbPort = port,
                DbSID = sid,
                DbConnectionStr = encrypCode.Encrypto(strCon),
                Description = ""
            });
            return list;
        }

        private List<string> GetContacts(FormCollection collection)
        {
            List<string> list = new List<string>();
            var userIDs = collection["Users"];
            if (string.IsNullOrEmpty(userIDs))
            {
                return list;
            }
            list = userIDs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
            return list;
        }

        private List<ETL_Target_ViewConfigInfo> GetSourceViewConfigs(string ids, string dbType, LoginInfo loginInfos)
        {
            List<ETL_Target_ViewConfigInfo> list = new List<ETL_Target_ViewConfigInfo>();
            ModelConfigBLL modelConfigBLL = new BLL.ModelConfigBLL(loginInfos);
            EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
            string[] idsArr = ids.Split(',');
            foreach (string id in idsArr)
            {
                if (dbType == "2")//海量数据实例 id来源是数据源表
                {
                    var sourceViewInfo = sourceViewConfigBLL.GetInfoById(id);
                    if (sourceViewInfo != null)
                    {
                        list.Add(new ETL_Target_ViewConfigInfo()
                        {
                            Guid = Guid.NewGuid().ToString(),
                            ModelViewID = sourceViewInfo.Guid,
                            MDPViewSchema = sourceViewInfo.MDP_ViewSchema,
                            MDP_ViewName = sourceViewInfo.DisplayName,
                            ViewName = Guid.NewGuid().ToString().Replace("-", ""),
                            ViewDescription = sourceViewInfo.ViewChnName,
                            WhereCondition = ""
                        });
                    }
                }
                else//普通实例，id来源是模型
                {
                    var modelConfigInfo = modelConfigBLL.GetItem(id);
                    if (modelConfigInfo != null)
                    {
                        list.Add(new ETL_Target_ViewConfigInfo()
                        {
                            Guid = Guid.NewGuid().ToString(),
                            ModelViewID = modelConfigInfo.GUID,
                            MDPViewSchema = modelConfigInfo.SchemaName,
                            MDP_ViewName = modelConfigInfo.TableName,
                            ViewName = Guid.NewGuid().ToString().Replace("-", ""),
                            ViewDescription = modelConfigInfo.TableDescription,
                            WhereCondition = ""
                        });
                    }
                }
            }
            return list;
        }

        private string DataTableToString(DataTable dt, Dictionary<string, string> columnNameDics, string sortField, string sortBy, string onColumnClick)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("");

            if (dt.Columns.Count > 0)
            {
                sb.AppendLine("<table id=\"tbSoutceTableData\" class=\"pagTableNofixed\" cellspacing=\"0\" cellpadding=\"0\">");
                sb.AppendLine("<tbody>");
                sb.AppendLine(" <tr class=\"pagTablehd\">");
                int width = 100 / dt.Columns.Count;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string textalign = "";
                    if (dt.Columns[i].DataType.Equals(typeof(int))
                        || dt.Columns[i].DataType.Equals(typeof(float))
                        || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                    {
                        textalign = "center";
                    }
                    else
                    {
                        textalign = "left";
                    }
                    string columnName = dt.Columns[i].ColumnName;
                    string columnCaption = dt.Columns[i].ColumnName;
                    if (columnNameDics != null && columnNameDics.ContainsKey(columnName.ToUpper()))
                    {
                        if (!string.IsNullOrEmpty(columnNameDics[columnName.ToUpper()]))
                        {
                            columnCaption = columnNameDics[columnName.ToUpper()];
                        }
                    }
                    sb.AppendLine(string.Format("     <td class=\"DataGrid2ViewName csscursor\" style=\"width: {0}%; text-align: {1};\" onclick=\"{5}('{2}',{4})\">"
                        , width, textalign
                        , columnName
                        , columnCaption
                        , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("1") ? "0" : "1") : "1"
                        , !string.IsNullOrEmpty(onColumnClick) ? onColumnClick : "initSourceTableData"
                        ));


                    sb.AppendLine(string.Format("         {1}{0}", columnCaption
                        , sortField.Equals(columnName, StringComparison.CurrentCultureIgnoreCase) ? (sortBy.Equals("1") ? "<img src='/images/datagrid_sort_asc.gif'>" : "<img src='/images/datagrid_sort_desc.gif'>") : ""
                        ));
                    sb.AppendLine("     </td>");
                }
                sb.AppendLine(" </tr>");
                int rowNumber = 0;
                foreach (DataRow rowItem in dt.Rows)
                {
                    if (rowNumber++ % 2 == 0)
                    {
                        sb.AppendLine(" <tr>");
                    }
                    else
                    {
                        sb.AppendLine(" <tr class=\"tr_c2\">");
                    }
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string textalign = "";
                        if (dt.Columns[i].DataType.Equals(typeof(int))
                            || dt.Columns[i].DataType.Equals(typeof(float))
                            || dt.Columns[i].DataType.Equals(typeof(DateTime)))
                        {
                            textalign = "center";
                        }
                        else
                        {
                            textalign = "left";
                        }

                        var value = rowItem[i] == null || string.IsNullOrEmpty(rowItem[i].ToString()) ? "&nbsp;" : rowItem[i].ToString();
                        int vlength = value.Length;
                        if (vlength > 200)
                            value = value.Substring(0, 200) + "...";
                        var title = value.Equals("&nbsp;") ? "" : string.Format("内容：{0}\n长度：{1}", value, vlength);
                        sb.AppendLine(string.Format("     <td style=\"text-align: {0};\" title=\"{1}\">", textalign, title));
                        //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                        {
                            sb.AppendLine("     <nobr>");
                        }
                        if (vlength > 50)
                            value = value.Substring(0, 50) + "...";
                        sb.AppendLine(string.Format("         {0}", value));
                        //if (dt.Columns[i].DataType.Equals(typeof(DateTime)))
                        {
                            sb.AppendLine("     </nobr>");
                        }
                        sb.AppendLine("     </td>");
                    }
                    sb.AppendLine(" </tr>");
                }
                sb.AppendLine("</tbody>");
                sb.AppendLine("</table>");
            }
            return sb.ToString();
        }

        #endregion
    }
}
